import pymysql

class Dict:
    def __init__(self):
        self.kwargs={
            'user':'root',
            'password' : 'root',
            'database' :'dict',
            'charset':'utf8'
        }
        self.db=pymysql.connect(**self.kwargs)
        self.cur=self.db.cursor()

    def close(self):
        self.cur.close()
        self.db.close()

    def register(self, name, password):
        try:
            sql="insert into user (name,password) values (%s,%s)"
            self.cur.execute(sql,[name,password])
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            self.db.rollback()
            return False


        pass

    def login(self, name, password):

        sql = "select * from user where name=%s and password=%s"
        self.cur.execute(sql, [name, password])
        self.db.commit()
        if self.cur.fetchone():
            return True
        else:
            return False

        pass

    def query_word(self, word):
        sql="select definition from words where word=%s"
        self.cur.execute(sql,[word])
        self.db.commit()
        mean=self.cur.fetchone()
        if mean:
            return mean
        else:
            return False
        pass

    def insert_hist(self, name,word):
        sql="select id from user where name=%s"
        self.cur.execute(sql,[name])
        user_id=self.cur.fetchone()[0]
        sql="select id from words where word=%s"
        self.cur.execute(sql,[word])
        word_id=self.cur.fetchone()[0]
        sql="insert into hist (word_id,user_id) values (%s,%s)"
        self.cur.execute(sql,[word_id,user_id])
        self.db.commit()
        pass

    def viewhistory(self, username):
        try:
            sql="select id from user where name=%s"
            self.cur.execute(sql,[username]) #get user_id to query word_id from hist then query word from words by word_id
            user_id=self.cur.fetchone()[0]

            sql='select word_id from hist where user_id=%s limit 10'
            self.cur.execute(sql,[user_id])
            wordlist=[]
            word_ids=self.cur.fetchall()

            for word_id in word_ids:
                sql='select word from words where id=%s'
                self.cur.execute(sql,[word_id])
                wordlist.append(self.cur.fetchone()[0])
            return wordlist
        except Exception as e:
            print(e)
            return []

        pass
